package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;

import model.Employee;
import model.EmployeeJob;

import util.MD5;
import util.Number;

public class DBEmployee extends DBPerson implements IFEmployee 
{
	private Connection con;

	public DBEmployee()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int createEmployee(Employee employee) throws SQLException
	{
		int rc = -1;
		int insert = super.createPerson(employee);

		if (insert > 0)
		{
			String query = "INSERT INTO employee VALUES(?, ?, ?, ?)";

			if (DBConnection.doDebug())
				DBConnection.printDebug("createEmployee", query);

			try
			{
				PreparedStatement ps = con.prepareStatement(query);

				ps.setInt(1, insert);
				ps.setString(2, employee.getfName());
				ps.setString(3, employee.getlName());
				ps.setInt(4, employee.getJob().getJobID());

				rc = ps.executeUpdate();

				if (rc > 0)
					rc = insert;

				ps.close();
			}
			catch (Exception e)
			{
				if (DBConnection.doDebug())
				{
					System.out.println("[ERROR] Query exception - createEmployee - INSERT: " + e);
					e.printStackTrace();
				}
			}
		}

		return rc;
	}

	@Override
	public int updateEmployee(Employee employee)
	{
		int rc = -1;
		int update = super.updatePerson(employee);

		if (update > 0)
		{
			String query = "UPDATE employee SET employee_fname = ?, employee_lname = ?, employee_job_id = ? WHERE employee_id = ?";

			if (DBConnection.doDebug())
				DBConnection.printDebug("updateEmployee", query);

			try 
			{
				PreparedStatement ps = con.prepareStatement(query);

				ps.setString(1, employee.getfName());
				ps.setString(2, employee.getlName());
				ps.setInt(3, employee.getJob().getJobID());
				ps.setInt(4, employee.getPersonID());

				rc = ps.executeUpdate();

				ps.close();
			}
			catch (Exception e)
			{
				if (DBConnection.doDebug())
				{
					System.out.println("[ERROR] Query exception - updateEmployee - UPDATE: " + e);
					e.printStackTrace();
				}
			}
		}

		return rc;
	}

	@Override
	public Employee searchEmployeeID(int employeeID) 
	{
		Employee newEmployee = new Employee();

		String query = "SELECT e.*, p.*, j.* FROM employee AS e LEFT JOIN person AS p ON e.employee_id = p.person_id LEFT JOIN employee_job AS j ON e.employee_job_id = j.employee_job_id WHERE e.employee_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchEmployeeID", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, employeeID);

			ResultSet results = ps.executeQuery();

			if (results.next())
			{
				newEmployee = createEmployeeObject(results);
				newEmployee.setPassword(results.getString("person_password"));	
			}

			ps.close();
		}
		catch(Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchEmployeeID - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newEmployee;
	}

	@Override
	public ArrayList<Employee> searchEmployeeUsername(boolean active, String jobName, String username) throws SQLException
	{
		return searchEmployees(active, jobName, username, "p.person_username LIKE ?", 1);		
	}

	@Override
	public ArrayList<Employee> searchEmployeePhoneNo(boolean active, String jobName, String phoneNo) throws SQLException
	{
		return searchEmployees(active, jobName, phoneNo, "p.person_phoneno LIKE ?", 1);		
	}

	@Override
	public ArrayList<Employee> searchEmployeeEmail(boolean active, String jobName, String email) throws SQLException
	{
		return searchEmployees(active, jobName, email, "p.person_email LIKE ?", 1);		
	}

	@Override
	public ArrayList<Employee> searchEmployeeName(boolean active, String jobName, String name)
	{
		return searchEmployees(active, jobName, name, "(e.employee_fname LIKE ? OR e.employee_lname LIKE ?)", 2);
	}

	@Override
	public ArrayList<Employee> searchEmployeeEverything(boolean active, String jobName, String search) throws SQLException
	{
		boolean number = Number.isNumber(search);

		return searchEmployees(active, jobName, search, "(p.person_username LIKE ? OR p.person_phoneno LIKE ? OR p.person_email LIKE ? OR e.employee_fname LIKE ? OR e.employee_lname LIKE ?" + (number ? " OR e.employee_id = ?" : "") + " )", (number ? 6 : 5));
	}

	@Override
	public int checkLogin(String loginUsername, char[] loginPassword)  throws SQLException
	{
		int employeeID = 0;

		String query = "SELECT person_id FROM person WHERE person_username = ? AND person_password = ? AND person_type = '0' AND person_active = '1'";

		if (DBConnection.doDebug())
			DBConnection.printDebug("checkLogin", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, loginUsername);
			ps.setString(2,MD5.md5(new String(loginPassword)));

			ResultSet result = ps.executeQuery();

			if (result.next())
				employeeID = result.getInt("person_id");

			ps.close();
		}
		catch(Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - checkLogin - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return employeeID;
	}

	/**
	 * Our method for handling employee search
	 * 
	 * @param 	boolean		are we searching for active only or not
	 * @param 	String		the job type we are searching for, can be empty
	 * @param	String 		our search word, we use it for the prepared statement
	 * @param 	String		our extra query (in order to keep everything in one method and not 500 lines of code..)
	 * @param 	int			might be kinda hardcoded, number of prepared statements we have to do, most (only) used in searchEmployeeEverything
	 * 
	 * @return  ArrayList<Employee>
	 */
	private ArrayList<Employee> searchEmployees(boolean active, String jobName, String search, String clause, int num) 
	{
		ResultSet results;
		ArrayList<Employee> employeeList = new ArrayList<Employee>();

		String query = "SELECT e.*, p.*, j.employee_job_name FROM employee AS e LEFT JOIN person AS p ON e.employee_id = p.person_id LEFT JOIN employee_job AS j ON e.employee_job_id = j.employee_job_id WHERE p.person_type = '0'" + (jobName.length() > 0 ? " AND j.employee_job_name = '" + jobName + "' " : "") + (active ? " AND p.person_active = '1'" : "") + ((clause.length() > 0) ? (" AND " + clause) : "") + " ORDER BY e.employee_fname ASC, e.employee_lname ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchEmployees", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			if (clause.length() > 0)
			{
				ps.setString(1, "%" + search + "%");

				if (num > 1)
				{
					for(int i = 2; i <= num; i++)
						ps.setString(i, (i == 6 ? search : "%" + search + "%"));						
				}
			}

			results = ps.executeQuery();

			while(results.next())
			{
				Employee newEmployee = createEmployeeObject(results);

				employeeList.add(newEmployee);
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchEmployees - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return employeeList;
	}

	private Employee createEmployeeObject(ResultSet results) throws SQLException
	{
		Employee newEmployee = new Employee();
		super.createPersonObject(newEmployee, results, false);

		newEmployee.setfName(results.getString("employee_fname"));
		newEmployee.setlName(results.getString("employee_lname"));
		newEmployee.setJob(new EmployeeJob(results.getInt("employee_job_id"), results.getString("employee_job_name")));

		return newEmployee;
	}
}